/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fresher.model;

import com.vng.jcore.profiler.ProfilerLog;
import fresher.objects.Table;
import fresher.utility.ConnectionObject;
import fresher.utility.ConnectionPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;



/**
 *
 * @author quangtm5
 */
public class TableManageModel {
    
    private ProfilerLog profiler = new ProfilerLog();
    private ConnectionPool connectionPool  = ConnectionPool.getConnectionPool();
    /**
     * Get all table and it's informations 
     * 
     * @return ArrayList<Table> all table in database 
     */
    
    
    public ArrayList<Table> getListTable()
    {
        ArrayList<Table> listOfTable = new ArrayList<>();
               
        
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT `id`, `status` "
                     + "FROM `table` ";
                     
        try 
        {
            profiler.doStartLog("queryGetList");
            ResultSet results = connectionObject.query(query);
            profiler.doEndLog("queryGetList");
            
            
            while(results.next())
            {
                Table table = new Table();
                table.setId(results.getInt("Id"));
                table.setStatus(results.getInt("status"));
                
                listOfTable.add(table);
            }
            results.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        //when finish task, set connectioObject free again to serve another request
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        
        return listOfTable;
    }
    
    /**
     * Get only one table which has id matches with id parameter number
     * 
     * @param id id of the table
     * @return Table 
     */
    public Table getTable(int id)
    {
        
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT id, status "
                     + "FROM `table` "
                     + "WHERE id=" + String.valueOf(id);
        
        Table table = null;
        try 
        {
            profiler.doStartLog("queryGetTable");
            ResultSet results = connectionObject.query(query);
            profiler.doEndLog("queryGetTable");
           
            while(results.next())
            {
                table = new Table();
                table.setId(results.getInt("Id"));
                table.setStatus(results.getInt("status"));
                
               
            }
            results.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        return table;
    }
    
    /**
     * Get the latest inserted table (max of id)
     * 
     * @return Table 
     */
    public Table getMaxIdTable()
    {
               
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT id, status "
                     + "FROM `table` "
                     + "ORDER BY id DESC LIMIT 1";
        
        Table table = null;
        try 
        {
            ResultSet results = connectionObject.query(query);
           
            while(results.next())
            {
                table = new Table();
                table.setId(results.getInt("Id"));
                table.setStatus(results.getInt("status"));
                
                results.close();
            }
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        return table;
    }
    
    /**
     * Insert a table in database
     * 
     * @return success or fail
     */
    public int insertTable()
    {
        
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        String query = "INSERT INTO `table` "
                     + "VALUES ( null, 0) ";
        
        int result = -1;
        try 
        {
            profiler.doStartLog("queryInsert");
            result = connectionObject.executeUpdate(query);
            profiler.doEndLog("queryInsert");
//            System.out.println(result);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        return result;
    }
    
    /**
     * Set a table free
     * 
     * @param id Id of the table
     * @return success or fail
     */
    public int setFreeTable(int id)
    {
                
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
         profiler.doEndLog("createConnection");
        
        String query = "UPDATE `table` "
                     + "SET `status` = 0 "
                     + "WHERE `id`=" + id;
        
        int result = -1;
        try 
        {
            profiler.doStartLog("querySetFree");
            result = connectionObject.executeUpdate(query);
            profiler.doEndLog("querySetFree");
//            System.out.println(result);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        
        return result;
    }

    /**
     * Set table full
     * 
     * @param id Id of table
     * @return success of fail
     */
    public int setFullTable(int id) 
    {
                
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        String query = "UPDATE `table` "
                     + "SET `status` = 1 "
                     + "WHERE `id`=" + id;
        
        int result = -1;
        try 
        {
            profiler.doStartLog("querySetFull");
            result = connectionObject.executeUpdate(query);
            profiler.doEndLog("querySetFull");
//            System.out.println(result);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        
        return result;
    }

    public int deleteTable(int id) 
    {
                
        profiler.doStartLog("createConnection");
        ConnectionObject connectionObject = this.connectionPool.getFreeConnectionObject();
        profiler.doEndLog("createConnection");
        
        String query = "DELETE FROM `table` "
                     + "WHERE `id`=" + id;
        
        int result = -1;
        try 
        {
            profiler.doStartLog("queryDelete");
            result = connectionObject.executeUpdate(query);
            profiler.doEndLog("queryDelete");
//            System.out.println(result);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(TableManageModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        profiler.doStartLog("closeConnection");
        connectionObject.setFree();
        profiler.doEndLog("closeConnection");
        
        System.out.print(profiler.dumpLogConsole());
        
        return result;
    }
}
